﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace nkstdgia.excel.export
{
    public class SpreedEdit
    {
        private SpreadsheetDocument m_doc;
        private String m_fileName;
        public FormatStyleIndex formatIndexs = null;

        public class FormatStyleIndex
        {
            public DocumentFormat.OpenXml.UInt32Value iThinBorderIndex;
            public DocumentFormat.OpenXml.UInt32Value iYellowFillIndex;
        }

        public String FileName
        {
            get { return m_fileName; }
            set { m_fileName = value; }
        }
        private WorksheetPart m_wsp;
        private String currSheetName;
        private Stylesheet styles;

        DocumentFormat.OpenXml.UInt32Value borderStyleIndex;

        public SpreedEdit(String fileName)
        {
            if (validateFilename(fileName))
                this.m_fileName = fileName;
            else
                this.m_fileName = null;

            m_doc = null;
            m_wsp = null;
        }

        private bool validateFilename(String fileName)
        {
            if (fileName == "")
                return false;
            return true;
        }

        public bool startEditSpreed(String sheetName)
        {
            getDocumentFromFile(m_fileName);
            if (m_doc == null)
                return false;
            m_wsp = getWorksheetPart(m_doc, sheetName);
            if (m_wsp == null)
                return false;
            currSheetName = sheetName;

            if (formatIndexs == null)
            {
                borderCellStyleIndex();
            }

            //borderStyleIndex = DocumentFormat.OpenXml.UInt32Value.FromUInt32((UInt32)borderCellStyleIndex());

            return true;
        }

        private void borderCellStyleIndex()
        {
            List<WorkbookStylesPart> wsList = new List<WorkbookStylesPart>();

            //WorkbookStylesPart stylesPart = m_doc.WorkbookPart.GetPartsOfType<WorkbookStylesPart>(wsList);
            m_doc.WorkbookPart.GetPartsOfType<WorkbookStylesPart>(wsList);

            if (wsList.Count > 0)
            {
                WorkbookStylesPart stylesPart = wsList[0];

                formatIndexs = new FormatStyleIndex();

                //only border cell
                Borders boders = stylesPart.Stylesheet.Borders;
                Border border = new Border();
                border.LeftBorder = new LeftBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin };
                border.RightBorder = new RightBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin };
                border.TopBorder = new TopBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin };
                border.BottomBorder = new BottomBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin };

                stylesPart.Stylesheet.Borders.Append(border);

                int ibordercell = stylesPart.Stylesheet.Borders.ChildElements.Count - 1;

                stylesPart.Stylesheet.CellFormats.Append(new CellFormat() { FontId = 0, FillId = 0, BorderId = DocumentFormat.OpenXml.UInt32Value.FromUInt32((uint)ibordercell), ApplyBorder = true });

                formatIndexs.iThinBorderIndex = DocumentFormat.OpenXml.UInt32Value.FromUInt32((uint)stylesPart.Stylesheet.CellFormats.ChildElements.Count) - 1;

                // add fill yellow and border cell

                stylesPart.Stylesheet.Fills.Append(new Fill(                                                           // Index 2 - The yellow fill.
                        new PatternFill(
                            new ForegroundColor() { Rgb = new HexBinaryValue() { Value = "FFFFFF00" } }
                        ) { PatternType = PatternValues.Solid }));

                uint yellowFillIndex = (uint)stylesPart.Stylesheet.Fills.ChildElements.Count - 1;

                stylesPart.Stylesheet.CellFormats.Append(new CellFormat() { FontId = 0, FillId = DocumentFormat.OpenXml.UInt32Value.FromUInt32(yellowFillIndex), BorderId = DocumentFormat.OpenXml.UInt32Value.FromUInt32((uint)ibordercell), ApplyFill = true });
                formatIndexs.iYellowFillIndex = DocumentFormat.OpenXml.UInt32Value.FromUInt32((uint)stylesPart.Stylesheet.CellFormats.ChildElements.Count) - 1;

                //stylesPart.Stylesheet = GenerateStyleSheet();
                stylesPart.Stylesheet.Save();
            }
        }

        private void getDocumentFromFile(String fileName)
        {
            m_doc = SpreadsheetDocument.Open(fileName, true);
            
        }

        public void endEditting()
        {
            m_wsp.Worksheet.Save();
            m_doc.Close();
            m_wsp = null;
            m_doc = null;
        }

        private WorksheetPart getWorksheetPart(SpreadsheetDocument doc, String sheetName)
        {
            IEnumerable<Sheet> sheets = doc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == sheetName);
            if (sheets.Count() == 0)
                return null;
            String relationShipID = sheets.First().Id.Value;
            WorksheetPart workSheetPart = (WorksheetPart)doc.WorkbookPart.GetPartById(relationShipID);
            return workSheetPart;
        }

        private Sheet getSheet(SpreadsheetDocument doc, String sheetName)
        {
            IEnumerable<Sheet> sheets = doc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == sheetName);
            if (sheets.Count() == 0)
                return null;
            return sheets.First();
        }

        private Cell getCellFromWorkSheet(Worksheet worksheet, String columnName, int rowIndex)
        {
            IEnumerable<Row> rows = worksheet.GetFirstChild<SheetData>().Elements<Row>().Where(r => r.RowIndex == rowIndex);
            if (rows.Count() == 0)
            {
                // A cell does not exist at the specified row.
                return null;
            }

            IEnumerable<Cell> cells = rows.First().Elements<Cell>().Where(c => string.Compare(c.CellReference.Value, columnName + rowIndex, true) == 0);
            if (cells.Count() == 0)
            {
                // A cell does not exist at the specified column, in the specified row.
                return null;
            }

            return cells.First();
        }

        private Row getRowFromWorkSheet(Worksheet worksheet, int rowIndex)
        {
            IEnumerable<Row> rows = worksheet.GetFirstChild<SheetData>().Elements<Row>().Where(r => r.RowIndex == rowIndex);
            if (rows.Count() == 0)
            {
                // A cell does not exist at the specified row.
                return null;
            }
            else
                return rows.First();
        }

        public Cell getCellFromFile(String fileName, String sheetName, String columnName, int rowIndex)
        {
            if (this.m_doc == null || m_wsp == null)
                startEditSpreed(sheetName);
            if (m_doc == null || m_wsp == null)
                return null;
            Cell cell = getCellFromWorkSheet(m_wsp.Worksheet, columnName, rowIndex);
            return cell;
        }

        //String cell
        public bool updateCellValue(Cell cellEdited, String workSheetName, String columnName, int rowIndex)
        {
            if (m_doc == null || m_wsp == null)
                return false;
            Cell cell = getCellFromWorkSheet(m_wsp.Worksheet, columnName, rowIndex);
            if (cell == null)
                return false;
            if (cellEdited.DataType != cell.DataType)
                return false;
            cell.CellValue = cellEdited.CellValue;

            m_wsp.Worksheet.Save();
            return true;
        }

        public bool updateCellValue(Cell cellEdited, String workSheetName, String columnName, int rowIndex, DocumentFormat.OpenXml.UInt32Value styleIndex)
        {
            if (m_doc == null || m_wsp == null)
                return false;
            Cell cell = getCellFromWorkSheet(m_wsp.Worksheet, columnName, rowIndex);
            if (cell == null)
                return false;
            if (cellEdited.DataType != cell.DataType)
                return false;
            cell.CellValue = cellEdited.CellValue;

            cell.StyleIndex = styleIndex;

            m_wsp.Worksheet.Save();
            return true;
        }

        public Text getCellValue(int id)
        {
            if (m_doc == null)
                return null;
            SharedStringItem shareString = GetSharedStringItemById(m_doc.WorkbookPart, id);
            return shareString.Text;
        }

        public static SharedStringItem GetSharedStringItemById(WorkbookPart workbookPart, int id)
        {
            SharedStringItem returnVL = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(id);
            return returnVL;
        }

        //int cell
        public Boolean updateCellValue(String columnName, int rowIndex, int data)
        {
            if (m_doc == null || m_wsp == null)
                return false;
            Cell cell = getCellFromWorkSheet(m_wsp.Worksheet, columnName, rowIndex);
            if (cell == null)
            {
                SheetData sheetData = m_wsp.Worksheet.GetFirstChild<SheetData>();
                if (sheetData == null)
                    return false;
                Row row = getRowFromWorkSheet(m_wsp.Worksheet, rowIndex);
                if (row == null)
                {
                    var r = new Row { RowIndex = (uint)rowIndex };
                    Cell c = new Cell();
                    c.CellReference = columnName + rowIndex;
                    c.DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.Number);
                    c.CellValue = new CellValue(data.ToString());
                    r.Append(c);
                    sheetData.Append(r);
                    return true;
                }
                Cell c1 = new Cell();
                c1.CellReference = columnName + rowIndex;
                c1.DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.String);
                c1.CellValue = new CellValue(data.ToString());
                row.Append(c1);
                return true;
            }
            cell.DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.Number);
            cell.CellValue = new CellValue(data.ToString());
            return true;
        }

        public Boolean updateCellValue(String columnName, int rowIndex, int data, DocumentFormat.OpenXml.UInt32Value styleIndex)
        {
            if (m_doc == null || m_wsp == null)
                return false;
            Cell cell = getCellFromWorkSheet(m_wsp.Worksheet, columnName, rowIndex);
            if (cell == null)
            {
                SheetData sheetData = m_wsp.Worksheet.GetFirstChild<SheetData>();
                if (sheetData == null)
                    return false;
                Row row = getRowFromWorkSheet(m_wsp.Worksheet, rowIndex);
                if (row == null)
                {
                    var r = new Row { RowIndex = (uint)rowIndex };
                    Cell c = new Cell();
                    c.CellReference = columnName + rowIndex;
                    c.DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.Number);
                    c.StyleIndex = styleIndex;
                    c.CellValue = new CellValue(data.ToString());
                    r.Append(c);
                    sheetData.Append(r);
                    return true;
                }
                Cell c1 = new Cell();
                c1.CellReference = columnName + rowIndex;
                c1.StyleIndex = styleIndex;
                c1.DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.String);
                c1.CellValue = new CellValue(data.ToString());
                row.Append(c1);
                return true;
            }
            cell.DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.Number);
            cell.CellValue = new CellValue(data.ToString());
            cell.StyleIndex = styleIndex;
            return true;
        }

        //double row
        public Boolean updateCellValue(String columnName, int rowIndex, double data, DocumentFormat.OpenXml.UInt32Value styleIndex)
        {
            if (m_doc == null || m_wsp == null)
                return false;
            String text = string.Format("{0:0.000}", data);
            Cell cell = getCellFromWorkSheet(m_wsp.Worksheet, columnName, rowIndex);
            if (cell == null)
            {
                SheetData sheetData = m_wsp.Worksheet.GetFirstChild<SheetData>();
                if (sheetData == null)
                    return false;
                Row row = getRowFromWorkSheet(m_wsp.Worksheet, rowIndex);
                if (row == null)
                {
                    var r = new Row { RowIndex = (uint)rowIndex };
                    Cell c = new Cell();
                    c.CellReference = columnName + rowIndex;
                    c.StyleIndex = styleIndex;
                    c.DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.Number);

                    c.CellValue = new CellValue(text.Replace(",", "."));
                    r.Append(c);
                    sheetData.Append(r);
                    return true;
                }
                Cell c1 = new Cell();
                c1.CellReference = columnName + rowIndex;
                c1.StyleIndex = styleIndex;
                c1.DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.Number);
                c1.CellValue = new CellValue(text.Replace(",", "."));
                row.Append(c1);
                return true;
            }

            cell.StyleIndex = styleIndex;
            cell.DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.Number);
            cell.CellValue = new CellValue(text.Replace(",", "."));
            return true;
        }

        public Boolean updateCellValue(String columnName, int rowIndex, double data)
        {
            if (m_doc == null || m_wsp == null)
                return false;

            String text = string.Format("{0:0.000}", data);
            Cell cell = getCellFromWorkSheet(m_wsp.Worksheet, columnName, rowIndex);
            if (cell == null)
            {
                SheetData sheetData = m_wsp.Worksheet.GetFirstChild<SheetData>();
                if (sheetData == null)
                    return false;
                Row row = getRowFromWorkSheet(m_wsp.Worksheet, rowIndex);
                if (row == null)
                {
                    var r = new Row { RowIndex = (uint)rowIndex };
                    Cell c = new Cell();
                    c.CellReference = columnName + rowIndex;
                    c.DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.Number);
                    
                    c.CellValue = new CellValue(text.Replace(",", "."));
                    r.Append(c);
                    sheetData.Append(r);
                    return true;
                }
                Cell c1 = new Cell();
                c1.CellReference = columnName + rowIndex;
                c1.DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.Number);
                c1.CellValue = new CellValue(text.Replace(",", "."));
                row.Append(c1);
                return true;
            }

            cell.DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.Number);
            cell.CellValue = new CellValue(text.Replace(",", "."));
            return true;
        }

        //String Data

        public Boolean updateCellValue(String columnName, int rowIndex, String data)
        {
            if (data == null)
                return false;
            if (m_doc == null || m_wsp == null)
                return false;

            Cell cell = getCellFromWorkSheet(m_wsp.Worksheet, columnName, rowIndex);
            if (cell == null)
            {
                SheetData sheetData = m_wsp.Worksheet.GetFirstChild<SheetData>();
                if (sheetData == null)
                    return false;
                Row row = getRowFromWorkSheet(m_wsp.Worksheet, rowIndex);
                if (row == null)
                {
                    var r = new Row { RowIndex = (uint)rowIndex };
                    Cell c = new Cell();
                    c.CellReference = columnName + rowIndex;
                    c.DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.String);

                    c.CellValue = new CellValue(data);
                    r.Append(c);
                    sheetData.Append(r);
                    return true;
                }
                Cell c1 = new Cell();
                c1.CellReference = columnName + rowIndex;
                c1.DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.String);
                c1.CellValue = new CellValue(data);
                row.Append(c1);
                return true;
            }
            cell.DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.String);
           
            cell.CellValue = new CellValue(data);
            return true;
        }

        public Boolean updateCellValue(String columnName, int rowIndex, String data, DocumentFormat.OpenXml.UInt32Value styleIndex)
        {
            if (data == null)
                return false;
            if (m_doc == null || m_wsp == null)
                return false;
            Cell cell = getCellFromWorkSheet(m_wsp.Worksheet, columnName, rowIndex);
            if (cell == null)
            {
                SheetData sheetData = m_wsp.Worksheet.GetFirstChild<SheetData>();
                if (sheetData == null)
                    return false;
                Row row = getRowFromWorkSheet(m_wsp.Worksheet, rowIndex);
                if (row == null)
                {
                    var r = new Row { RowIndex = (uint)rowIndex };
                    Cell c = new Cell();
                    c.CellReference = columnName + rowIndex;
                    c.DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.String);
                    c.StyleIndex = styleIndex;
                    c.CellValue = new CellValue(data);
                    r.Append(c);
                    sheetData.Append(r);
                    return true;
                }
                Cell c1 = new Cell();
                c1.CellReference = columnName + rowIndex;
                c1.StyleIndex = styleIndex;
                c1.DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.String);
                c1.CellValue = new CellValue(data);
                row.Append(c1);
                return true;
            }



            cell.DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.String);

            cell.CellValue = new CellValue(data);
            cell.StyleIndex = styleIndex;
            return true;
        }

        //datetime data

        public Boolean updateCellValue(String columnName, int rowIndex, DateTime data)
        {
            if (data == null)
                return false;

            if (m_doc == null || m_wsp == null)
                return false;
            Cell cell = getCellFromWorkSheet(m_wsp.Worksheet, columnName, rowIndex);
            if (cell == null)
            {
                SheetData sheetData = m_wsp.Worksheet.GetFirstChild<SheetData>();
                if (sheetData == null)
                    return false;
                Row row = getRowFromWorkSheet(m_wsp.Worksheet, rowIndex);
                if (row == null)
                {
                    var r = new Row { RowIndex = (uint)rowIndex };
                    Cell c = new Cell();
                    c.CellReference = columnName + rowIndex;
                    c.DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.String);
                    c.CellValue = new CellValue(data.Day.ToString() + "/" + data.Month + "/" + data.Year);
                    r.Append(c);
                    sheetData.Append(r);
                    return true;
                }
                Cell c1 = new Cell();
                c1.CellReference = columnName + rowIndex;
                c1.DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.String);
                c1.CellValue = new CellValue(data.Day.ToString() + "/" + data.Month + "/" + data.Year);
                row.Append(c1);
                return true;
            }
            cell.DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.String);
            cell.CellValue = new CellValue(data.Day.ToString() + "/" + data.Month + "/" + data.Year);
            return true;
        }

        public Boolean updateCellValue(String columnName, int rowIndex, DateTime data, DocumentFormat.OpenXml.UInt32Value styleIndex)
        {
            if (data == null)
                return false;

            if (m_doc == null || m_wsp == null)
                return false;
            Cell cell = getCellFromWorkSheet(m_wsp.Worksheet, columnName, rowIndex);
            if (cell == null)
            {
                SheetData sheetData = m_wsp.Worksheet.GetFirstChild<SheetData>();
                if (sheetData == null)
                    return false;
                Row row = getRowFromWorkSheet(m_wsp.Worksheet, rowIndex);
                if (row == null)
                {
                    var r = new Row { RowIndex = (uint)rowIndex };
                    Cell c = new Cell();
                    c.CellReference = columnName + rowIndex;
                    c.StyleIndex = styleIndex;
                    c.DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.String);
                    c.CellValue = new CellValue(data.Day.ToString() + "/" + data.Month + "/" + data.Year);
                    r.Append(c);
                    sheetData.Append(r);
                    return true;
                }
                Cell c1 = new Cell();
                c1.CellReference = columnName + rowIndex;
                c1.StyleIndex = styleIndex;
                c1.DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.String);
                c1.CellValue = new CellValue(data.Day.ToString() + "/" + data.Month + "/" + data.Year);
                row.Append(c1);
                return true;
            }
            cell.StyleIndex = styleIndex;
            cell.DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.String);
            cell.CellValue = new CellValue(data.Day.ToString() + "/" + data.Month + "/" + data.Year);
            return true;
        }

        static int tableId;

        public bool createNewWorkSheet(String sourceSheetName, String cloneSheetName)
        {
            //open file
            getDocumentFromFile(m_fileName);

            // get source work sheet part
            WorkbookPart workbookPart = m_doc.WorkbookPart;
            //Get the source sheet to be copied
            WorksheetPart sourceSheetPart = GetWorkSheetPart(workbookPart, sourceSheetName);

            //Take advantage of AddPart for deep cloning
            SpreadsheetDocument tempSheet = SpreadsheetDocument.Create(new MemoryStream(), m_doc.DocumentType);
            WorkbookPart tempWorkbookPart = tempSheet.AddWorkbookPart();
            WorksheetPart tempWorksheetPart = tempWorkbookPart.AddPart<WorksheetPart>(sourceSheetPart);
            //Add cloned sheet and all associated parts to workbook
            WorksheetPart clonedSheet = workbookPart.AddPart<WorksheetPart>(tempWorksheetPart);

            //Table definition parts are somewhat special and need unique ids...so let's make an id based on count
            int numTableDefParts = sourceSheetPart.GetPartsCountOfType<TableDefinitionPart>();
            tableId = numTableDefParts;
            //Clean up table definition parts (tables need unique ids)
            if (numTableDefParts != 0)
                FixupTableParts(clonedSheet, numTableDefParts);
            //There should only be one sheet that has focus
            CleanView(clonedSheet);

            //Add new sheet to main workbook part
            Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
            Sheet copiedSheet = new Sheet();
            copiedSheet.Name = cloneSheetName;
            copiedSheet.Id = workbookPart.GetIdOfPart(clonedSheet);
            copiedSheet.SheetId = (uint)sheets.ChildElements.Count + 1;
            sheets.Append(copiedSheet);
            //Save Changes
            workbookPart.Workbook.Save();
            m_doc.Close();
            return true;
        }

        static void CleanView(WorksheetPart worksheetPart)
        {
            //There can only be one sheet that has focus
            SheetViews views = worksheetPart.Worksheet.GetFirstChild<SheetViews>();
            if (views != null)
            {
                views.Remove();
                worksheetPart.Worksheet.Save();
            }
        }

        static void FixupTableParts(WorksheetPart worksheetPart, int numTableDefParts)
        {
            //Every table needs a unique id and name
            foreach (TableDefinitionPart tableDefPart in worksheetPart.TableDefinitionParts)
            {
                tableId++;
                tableDefPart.Table.Id = (uint)tableId;
                tableDefPart.Table.DisplayName = "CopiedTable" + tableId;
                tableDefPart.Table.Name = "CopiedTable" + tableId;
                tableDefPart.Table.Save();
            }
        }
        static WorksheetPart GetWorkSheetPart(WorkbookPart workbookPart, string sheetName)
        {
            //Get the relationship id of the sheetname
            string relId = workbookPart.Workbook.Descendants<Sheet>()
            .Where(s => s.Name.Value.Equals(sheetName))
            .First()
            .Id;
            return (WorksheetPart)workbookPart.GetPartById(relId);
        }

        #region BorderSheet

        public Boolean borderCellBlock(String sSheetName, int topColumnIndex, int topRowIndex, int bottomColumnIndex, int bottomRowIndex)
        {
            if (m_doc == null || m_wsp == null || (topColumnIndex > bottomColumnIndex) || (topRowIndex > bottomRowIndex))
                return false;
            for (int i = topColumnIndex; i <= bottomColumnIndex; i++)
            {
                string columnName = GetExcelColumnName(i);
                for (int j = topRowIndex; j <= bottomRowIndex; j++)
                {
                    Cell cell = getCellFromWorkSheet(m_wsp.Worksheet, columnName, j);
                    if (cell != null)
                        cell.StyleIndex = this.formatIndexs.iThinBorderIndex;
                    else
                    {
                        Row row = getRowFromWorkSheet(m_wsp.Worksheet, j);
                        Cell blankCell = new Cell();
                        blankCell.CellReference = columnName + j;
                        blankCell.StyleIndex = this.formatIndexs.iThinBorderIndex;
                        row.Append(blankCell);
                    }
                }
            }
            /*
            _excelApp = new Excel.Application();

            object misValue = System.Reflection.Missing.Value;

            Excel.Workbook workBook = _excelApp.Workbooks.Open(m_fileName,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing);

            if (workBook == null)
            {
                return false;
            }

            Excel.Worksheet sheet = (Excel.Worksheet)workBook.Sheets[sSheetName];

            if (sheet == null)
                return false;



            Excel.Range range = sheet.get_Range(topCell, bottomCell);

            if (range == null)
                return false;

            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.Black.ToArgb();
            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Color = System.Drawing.Color.Black.ToArgb();
            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.Color.Black.ToArgb();
            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.Color.Black.ToArgb();
            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Color = System.Drawing.Color.Black.ToArgb();
            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Color = System.Drawing.Color.Black.ToArgb();
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

            workBook.Save();
            workBook.Close(true, misValue, misValue);
            _excelApp.Quit();
            return true;
            */
            return true;
        }

        #endregion BorderSheet

        public Boolean initCellsBlockWithStyle(String sSheetName, int topColumnIndex, int topRowIndex, int bottomColumnIndex, int bottomRowIndex, DocumentFormat.OpenXml.UInt32Value styleIndex)
        {
            if (m_doc == null || m_wsp == null || (topColumnIndex > bottomColumnIndex) || (topRowIndex > bottomRowIndex))
                return false;
            SheetData sheetData = m_wsp.Worksheet.GetFirstChild<SheetData>();
            if (sheetData == null)
                return false;
            for (int i = topRowIndex; i <= bottomRowIndex; i++)
            {
                Row row = getRowFromWorkSheet(m_wsp.Worksheet, i);
                if (row == null)
                {
                    var r = new Row { RowIndex = (uint)i };
                    for (int j = topColumnIndex; j <= bottomColumnIndex; j++)
                    {
                        string columnName = GetExcelColumnName(j);
                        Cell c = new Cell();
                        c.CellReference = columnName + i;
                        c.StyleIndex = styleIndex;
                        r.Append(c);
                    }
                    sheetData.Append(r);
                }
            }

            return true;
        }

        #region deletesheet

        public void DeleteAWorkSheet(string sheetToDelete)
        {
            //open file
            if (m_doc == null)
                getDocumentFromFile(m_fileName);

            string Sheetid = "";
            WorkbookPart wbPart = m_doc.WorkbookPart;

            // Get the pivot Table Parts
            IEnumerable<PivotTableCacheDefinitionPart> pvtTableCacheParts = wbPart.PivotTableCacheDefinitionParts;
            Dictionary<PivotTableCacheDefinitionPart, string> pvtTableCacheDefinationPart = new Dictionary<PivotTableCacheDefinitionPart, string>();
            foreach (PivotTableCacheDefinitionPart Item in pvtTableCacheParts)
            {
                PivotCacheDefinition pvtCacheDef = Item.PivotCacheDefinition;
                //Check if this CacheSource is linked to SheetToDelete
                var pvtCahce = pvtCacheDef.Descendants<CacheSource>().Where(s => s.WorksheetSource.Sheet == sheetToDelete);
                if (pvtCahce.Count() > 0)
                {
                    pvtTableCacheDefinationPart.Add(Item, Item.ToString());
                }
            }
            foreach (var Item in pvtTableCacheDefinationPart)
            {
                wbPart.DeletePart(Item.Key);
            }
            //Get the SheetToDelete from workbook.xml
            Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetToDelete).FirstOrDefault();
            if (theSheet == null)
            {
                // The specified sheet doesn't exist.
            }
            //Store the SheetID for the reference
            Sheetid = theSheet.SheetId;

            // Remove the sheet reference from the workbook.
            WorksheetPart worksheetPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
            theSheet.Remove();

            // Delete the worksheet part.
            wbPart.DeletePart(worksheetPart);

            //Get the DefinedNames
            var definedNames = wbPart.Workbook.Descendants<DefinedNames>().FirstOrDefault();
            if (definedNames != null)
            {
                foreach (DefinedName Item in definedNames)
                {
                    // This condition checks to delete only those names which are part of Sheet in question
                    if (Item.Text.Contains(sheetToDelete + "!"))
                        Item.Remove();
                }
            }
            // Get the CalculationChainPart 
            //Note: An instance of this part type contains an ordered set of references to all cells in all worksheets in the 
            //workbook whose value is calculated from any formula

            CalculationChainPart calChainPart;
            calChainPart = wbPart.CalculationChainPart;
            if (calChainPart != null)
            {
                var calChainEntries = calChainPart.CalculationChain.Descendants<CalculationCell>().Where(c => c.SheetId == Sheetid);
                foreach (CalculationCell Item in calChainEntries)
                {
                    Item.Remove();
                }
                if (calChainPart.CalculationChain.Count() == 0)
                {
                    wbPart.DeletePart(calChainPart);
                }
            }

            // Save the workbook.
            wbPart.Workbook.Save();
            m_doc.Close();
        }

        #endregion deletesheet

        public Boolean renameCurrentSheet(String newName)
        {
            if (m_doc == null || m_wsp == null)
                return false;
            if (newName != null && newName.Trim() != "")
            {
                Sheet currentSheet = getSheet(this.m_doc, this.currSheetName);
                if (currSheetName != null)
                    currentSheet.Name = newName;
                else
                    return false;
            }
            else
                return false;
            return true;
        }

        public void MergeCells(List<String> dsMercells)
        {

            if (m_doc == null || m_wsp == null)
                return;
            MergeCells merCells = m_wsp.Worksheet.GetFirstChild<MergeCells>();
            if (merCells != null)
            {
                foreach (String s in dsMercells)
                {
                    String[] cells = s.Split('-');
                    MergeCell mercell = new MergeCell();
                    mercell.Reference = cells[0] + ":" + cells[1];
                    merCells.Append(mercell);
                }
            }
            

            /*_excelApp = new Excel.Application();

            object misValue = System.Reflection.Missing.Value;

            Excel.Workbook workBook = _excelApp.Workbooks.Open(m_fileName,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing);

            if (workBook == null)
            {
                return;
            }

            Excel.Worksheet sheet = (Excel.Worksheet)workBook.Sheets[sheetName];

            if (sheet == null)
                return;



            Excel.Range range = sheet.get_Range(cellFrom, cellTo);

            if (range == null)
                return;

            range.Merge(misValue);

            workBook.Save();
            workBook.Close(true, misValue, misValue);
            _excelApp.Quit();
            return;*/
        }

        /*
        public void FillBackgroundColor(String sheetName, String cellFrom, String cellTo)
        {
            _excelApp = new Excel.Application();

            object misValue = System.Reflection.Missing.Value;

            Excel.Workbook workBook = _excelApp.Workbooks.Open(m_fileName,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing);

            if (workBook == null)
            {
                return;
            }

            Excel.Worksheet sheet = (Excel.Worksheet)workBook.Sheets[sheetName];

            if (sheet == null)
                return;



            Excel.Range range = sheet.get_Range(cellFrom, cellTo);

            if (range == null)
                return;

            range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);

            workBook.Save();
            workBook.Close(true, misValue, misValue);
            _excelApp.Quit();
            return;
        }
        */
        public Boolean updateCellAndBorder(String columnName, int rowIndex, String data)
        {
            if (data == null)
                return false;
            if (m_doc == null || m_wsp == null)
                return false;

            

            Cell cell = getCellFromWorkSheet(m_wsp.Worksheet, columnName, rowIndex);
            if (cell == null)
                return false;
            cell.DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.String);

            cell.CellValue = new CellValue(data);

            cell.StyleIndex = borderStyleIndex;

            return true;
        }
        /******/
        private Stylesheet GenerateStyleSheet()
        {
            return new Stylesheet(
                new Fonts(
                    new Font(                                                               // Index 0 - The default font.
                        new FontSize(){ Val = 11 },
                        new Color(){ Rgb = new HexBinaryValue() { Value = "000000" } },
                        new FontName(){ Val = "Calibri" }),
                    new Font(                                                               // Index 1 - The bold font.
                        new Bold(),
                        new FontSize(){ Val = 11 },
                        new Color(){ Rgb = new HexBinaryValue() { Value = "000000" } },
                        new FontName(){ Val = "Calibri" }),
                    new Font(                                                               // Index 2 - The Italic font.
                        new Italic(),
                        new FontSize(){ Val = 11 },
                        new Color(){ Rgb = new HexBinaryValue() { Value = "000000" } },
                        new FontName(){ Val = "Calibri" }),
                    new Font(                                                               // Index 2 - The Times Roman font. with 16 size
                        new FontSize(){ Val = 16 },
                        new Color(){ Rgb = new HexBinaryValue() { Value = "000000" } },
                        new FontName(){ Val = "Times New Roman" })
                ),
                new Fills(
                    new Fill(                                                           // Index 0 - The default fill.
                        new PatternFill(){ PatternType = PatternValues.None }),
                    new Fill(                                                           // Index 1 - The default fill of gray 125 (required)
                        new PatternFill(){ PatternType = PatternValues.Gray125}),     
                    new Fill(                                                           // Index 2 - The yellow fill.
                        new PatternFill(
                            new ForegroundColor(){ Rgb = new HexBinaryValue() { Value = "FFFFFF00"} }
                        ){ PatternType = PatternValues.Solid })
                ),
                new Borders(
                    new Border(                                                         // Index 0 - The default border.
                        new LeftBorder(),
                        new RightBorder(),
                        new TopBorder(),
                        new BottomBorder(),
                        new DiagonalBorder()),
                    new Border(                                                         // Index 1 - Applies a Left, Right, Top, Bottom border to a cell
                        new LeftBorder(
                            new Color(){ Auto = true }
                        ){ Style = BorderStyleValues.Thin },
                        new RightBorder(
                            new Color(){ Auto = true }
                        ){ Style = BorderStyleValues.Thin },
                        new TopBorder(
                            new Color(){ Auto = true }
                        ){ Style = BorderStyleValues.Thin },
                        new BottomBorder(
                            new Color(){ Auto = true }
                        ){ Style = BorderStyleValues.Thin },
                        new DiagonalBorder())
                ),
                new CellFormats(
                    new CellFormat(){ FontId = 0, FillId = 0, BorderId = 0},                          // Index 0 - The default cell style.  If a cell does not have a style index applied it will use this style combination instead
                    new CellFormat(){ FontId = 1, FillId = 0, BorderId = 0, ApplyFont = true },       // Index 1 - Bold 
                    new CellFormat(){ FontId = 2, FillId = 0, BorderId = 0, ApplyFont = true },       // Index 2 - Italic
                    new CellFormat(){ FontId = 3, FillId = 0, BorderId = 0, ApplyFont = true },       // Index 3 - Times Roman
                    new CellFormat(){ FontId = 0, FillId = 2, BorderId = 0, ApplyFill = true },       // Index 4 - Yellow Fill
                    new CellFormat(                                                                   // Index 5 - Alignment
                        new Alignment(){ Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center }
                    ){ FontId = 0, FillId = 0, BorderId = 0, ApplyAlignment = true },
                    new CellFormat(){ FontId = 0, FillId = 0, BorderId = 1, ApplyBorder = true }      // Index 6 - Border
                )
            ); // return
        }
        /******/

        private string GetExcelColumnName(int columnNumber)
        {
            int dividend = columnNumber;
            string columnName = String.Empty;
            int modulo;

            while (dividend > 0)
            {
                modulo = (dividend - 1) % 26;
                columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
                dividend = (int)((dividend - modulo) / 26);
            }

            return columnName;
        }
    }
}